package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.Constants;
import com.hellogood.yaohuo.shared.entity.Detail;
import com.hellogood.yaohuo.shared.entity.Form;
import com.hellogood.yaohuo.shared.entity.ZyDetail;
import com.hellogood.yaohuo.shared.entity.ZyForm;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class ZyFormDAO extends FormDAO {
	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {

		List<ZyForm> formList = new ArrayList<ZyForm>();
		if (rs != null) {
			while (rs.next()) {
				ZyForm form = new ZyForm();
				form.setZybillNO(rs.getString("ZybillNO") == null ? "" : rs.getString("ZybillNO").trim());
				form.setBillNo(rs.getString("billNo") == null ? "" : rs.getString("billNo").trim());
				form.setZyDate(rs.getString("ZyDate") == null ? "" : rs.getString("ZyDate").trim());
				form.setOrgCode(rs.getString("OrgCode") == null ? "" : rs.getString("OrgCode").trim());
				form.setOrgName(rs.getString("OrgName") == null ? "" : rs.getString("OrgName").trim());
				form.setBillDate(rs.getString("ZyDate") == null ? "" : rs.getString("ZyDate").trim());
				form.setCounts(rs.getInt("Counts"));
				form.setStaffCode(rs.getString("StaffCode") == null ? "" : rs.getString("StaffCode").trim());
				form.setStaffName(rs.getString("StaffName") == null ? "" : rs.getString("StaffName").trim());
				form.setTimeMark(rs.getString("TimeMark") == null ? "" : rs.getString("TimeMark").trim());
				form.setRzDate(rs.getString("RzDate") == null ? "" : rs.getString("RzDate").trim());
				form.setRemark(rs.getString("Remark") == null ? "" : rs.getString("Remark").trim());
				formList.add(form);
			}
		}
		return formList;

	}

	public String createZiyou(ZyForm form, boolean isModify) {

		String result = "";
		List<String> sqls = new ArrayList<String>();
		if (isModify) {
			String sql = "DELETE FROM ZyForm WHERE ZybillNO = '" + form.getBillNo() + "'";
			sqls.add(sql);
			sql = "DELETE FROM ZyDetail WHERE ZybillNo = '" + form.getBillNo() + "'";
			sqls.add(sql);
		}
		String sql = "INSERT INTO ZyForm(ZybillNO,ZyDate,OrgCode,counts,staffCode,staffName,RzDate,Remark,createTime) VALUES('" + form.getBillNo() + "',CONVERT(varchar(100), GETDATE(), 23),'"
				+ form.getOrgCode() + "'," + form.getCounts() + ",'" + form.getStaffCode() + "','" + form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "',getDate())";
		sqls.add(sql);

		List<Detail> list = form.getList();
		if (list != null && (!list.isEmpty())) {
			for (Detail detail : list) {
				ZyDetail d = (ZyDetail) detail;
				sql = "INSERT INTO ZyDetail(ZybillNo,ZyCode,counts,remark) VALUES('" + d.getBillNo() + "','" + d.getZyCode() + "'," + d.getCounts() + ",'" + d.getRemark() + "')";
				sqls.add(sql);
			}
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;

	}

	@SuppressWarnings("unchecked")
	public List<Form> searchForm(String orgCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,f.*,f.ZyBillNo AS billNo FROM ZyForm f WHERE f.OrgCode = '" + orgCode
				+ "' AND cast(f.ZyDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate + "'),120) and cast(f.ZyDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	@SuppressWarnings("unchecked")
	public List<Form> searchForm(String tableFormName, String billNoField, String dateField, String orgCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM "
				+ tableFormName + " f WHERE (f.OrgCode = '" + orgCode + "' OR f.VendorCode = '" + orgCode + "') AND cast(f." + dateField + " as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ startDate + "'),120) and cast(f." + dateField + " as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	@SuppressWarnings("unchecked")
	public List<Form> searchFormByQuyu(String quyuCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}

		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,f.*,f.ZybillNo AS billNo,f.ZyDate AS billDate FROM ZyForm f WHERE (f.OrgCode IN ("
				+ orgs + ")) AND cast(f.ZyDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate + "'),120) and cast(f.ZyDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	public Form getFormByBillNo(String billNo) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName, f.*,f.ZybillNo AS billNo,f.zyDate AS billDate FROM ZyForm f WHERE f.ZybillNo = '"
				+ billNo + "'";
		List<?> list = super.query(sql);
		if (list != null && (!list.isEmpty())) {
			return (Form) list.get(0);
		}
		return null;
	}

	public String deleteForm(String billNo) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		String sql = "DELETE FROM zyform WHERE ZyBillNO = '" + billNo + "'";
		sqls.add(sql);
		sql = "DELETE FROM Zydetail WHERE ZyBillNO = '" + billNo + "'";
		sqls.add(sql);
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			System.out.println(sqls);
			log.error(sqls);
			log.error(e.getMessage());
			e.printStackTrace();
			return e.getMessage();
		}
		return result;
	}
	
	public String rz(String bill) throws DatabaseException {
		String result = "";
		String sql = "UPDATE zyForm SET rzDate = CONVERT(VARCHAR(10),getDate(),120) WHERE ZybillNo = '" + bill + "'";
		if(super.execute(sql)>0){
			
		}else{
			result = "入账失败";
		}
		return result;
	}
}
